/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.FinaMetaVO;
import com.informet.ipd.modelo.vo.FinaMetaMVO;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class FinaMetaDao extends DBase {

    Connection conn = null;

    public FinaMetaDao() {
   super();
    }


    private void setMuestraF(FinaMetaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODFINAMETA_PK(rs.getInt("NUM_CODFINAMETA_PK"));
        //bean.setTotal(rs.getDouble("total"));
    }
    private void setMuestraFM(FinaMetaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODFINAMETA_PK(rs.getInt("NUM_CODFINAMETAM_PK"));
        //bean.setTotal(rs.getDouble("total"));
    }


    private void setFinaMeta(FinaMetaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODFINAMETA_PK(rs.getInt("id"));
        bean.setTX_DESCACTPER(rs.getString("descAct"));
        bean.setNUM_CODACTPER_PK(rs.getInt("idAct"));
        bean.setPPto_Anual(rs.getDouble("PPto_Anual"));
        bean.setSemestreI(rs.getDouble("SemestreI"));
        bean.setSemestreII(rs.getDouble("SemestreII"));
        bean.setNUM_MES1FINAMETA(rs.getDouble("NUM_MES1FINAMETA"));
        bean.setNUM_MES2FINAMETA(rs.getDouble("NUM_MES2FINAMETA"));
        bean.setNUM_MES3FINAMETA(rs.getDouble("NUM_MES3FINAMETA"));
        bean.setNUM_MES4FINAMETA(rs.getDouble("NUM_MES4FINAMETA"));
        bean.setNUM_MES5FINAMETA(rs.getDouble("NUM_MES5FINAMETA"));
        bean.setNUM_MES6FINAMETA(rs.getDouble("NUM_MES6FINAMETA"));
        bean.setNUM_MES7FINAMETA(rs.getDouble("NUM_MES7FINAMETA"));
        bean.setNUM_MES8FINAMETA(rs.getDouble("NUM_MES8FINAMETA"));
        bean.setNUM_MES9FINAMETA(rs.getDouble("NUM_MES9FINAMETA"));
        bean.setNUM_MES10FINAMETA(rs.getDouble("NUM_MES10FINAMETA"));
        bean.setNUM_MES11FINAMETA(rs.getDouble("NUM_MES11FINAMETA"));
        bean.setNUM_MES12FINAMETA(rs.getDouble("NUM_MES12FINAMETA"));
    }



    private void setFinaMetaM(FinaMetaMVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODFINAMETAM_PK(rs.getInt("id"));
        bean.setTX_DESCACTPER(rs.getString("descAct"));
        bean.setNUM_CODACTPER_PK(rs.getInt("idAct"));
        bean.setPPto_Anual(rs.getDouble("PPto_Anual"));
        bean.setSemestreI(rs.getDouble("SemestreI"));
        bean.setSemestreII(rs.getDouble("SemestreII"));
        bean.setNUM_MES1FINAMETAM(rs.getDouble("NUM_MES1FINAMETAM"));
        bean.setNUM_MES2FINAMETAM(rs.getDouble("NUM_MES2FINAMETAM"));
        bean.setNUM_MES3FINAMETAM(rs.getDouble("NUM_MES3FINAMETAM"));
        bean.setNUM_MES4FINAMETAM(rs.getDouble("NUM_MES4FINAMETAM"));
        bean.setNUM_MES5FINAMETAM(rs.getDouble("NUM_MES5FINAMETAM"));
        bean.setNUM_MES6FINAMETAM(rs.getDouble("NUM_MES6FINAMETAM"));
        bean.setNUM_MES7FINAMETAM(rs.getDouble("NUM_MES7FINAMETAM"));
        bean.setNUM_MES8FINAMETAM(rs.getDouble("NUM_MES8FINAMETAM"));
        bean.setNUM_MES9FINAMETAM(rs.getDouble("NUM_MES9FINAMETAM"));
        bean.setNUM_MES10FINAMETAM(rs.getDouble("NUM_MES10FINAMETAM"));
        bean.setNUM_MES11FINAMETAM(rs.getDouble("NUM_MES11FINAMETAM"));
        bean.setNUM_MES12FINAMETAM(rs.getDouble("NUM_MES12FINAMETAM"));
    }


    public String spMan(int tipo,FinaMetaVO bean) {
        String msj = "";
        //JOptionPane.showMessageDialog(null, bean.getTX_TIPOFISIMETA());
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call manFinaMeta(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODFINAMETA_PK());
            cs.setDouble(3, bean.getNUM_MES1FINAMETA());
            cs.setDouble(4, bean.getNUM_MES2FINAMETA());
            cs.setDouble(5, bean.getNUM_MES3FINAMETA());
            cs.setDouble(6, bean.getNUM_MES4FINAMETA());
            cs.setDouble(7, bean.getNUM_MES5FINAMETA());
            cs.setDouble(8, bean.getNUM_MES6FINAMETA());
            cs.setDouble(9, bean.getNUM_MES7FINAMETA());
            cs.setDouble(10, bean.getNUM_MES8FINAMETA());
            cs.setDouble(11, bean.getNUM_MES9FINAMETA());
            cs.setDouble(12, bean.getNUM_MES10FINAMETA());
            cs.setDouble(13, bean.getNUM_MES11FINAMETA());
            cs.setDouble(14, bean.getNUM_MES12FINAMETA());
            cs.setDouble(15, bean.getNUM_CODACTPER_PK());
            cs.execute();
        } catch (Exception e) {
            msj = e.toString();
            return msj;
        }
        return msj;
    }



    public String spManM(int tipo,FinaMetaMVO bean) {
        String msj = "";
        //JOptionPane.showMessageDialog(null, bean.getTX_TIPOFISIMETA());
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call manFinaMetaM(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODFINAMETAM_PK());
            cs.setDouble(3, bean.getNUM_MES1FINAMETAM());
            cs.setDouble(4, bean.getNUM_MES2FINAMETAM());
            cs.setDouble(5, bean.getNUM_MES3FINAMETAM());
            cs.setDouble(6, bean.getNUM_MES4FINAMETAM());
            cs.setDouble(7, bean.getNUM_MES5FINAMETAM());
            cs.setDouble(8, bean.getNUM_MES6FINAMETAM());
            cs.setDouble(9, bean.getNUM_MES7FINAMETAM());
            cs.setDouble(10, bean.getNUM_MES8FINAMETAM());
            cs.setDouble(11, bean.getNUM_MES9FINAMETAM());
            cs.setDouble(12, bean.getNUM_MES10FINAMETAM());
            cs.setDouble(13, bean.getNUM_MES11FINAMETAM());
            cs.setDouble(14, bean.getNUM_MES12FINAMETAM());
            cs.setDouble(15, bean.getNUM_CODACTPER_PK());
            cs.execute();
        } catch (Exception e) {
            msj = e.toString();
            return msj;
        }
        return msj;
    }

    /*public ArrayList<FisiMetaVO> selectLista() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<OEstrategicoVO> lista = new ArrayList<OEstrategicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODOBJ_PK,TX_DESCOBJ from ME_OBJ";
            rs = stmt.executeQuery(sql);
            OEstrategicoVO bean = null;
            while (rs.next()) {
                bean = new OEstrategicoVO();
                setOEstrategico(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }*/

    public List getlist() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FinaMetaVO> lista = new ArrayList<FinaMetaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODFINAMETA_PK,NUM_CODACTPER_PK," +
                    "NUM_MES1FINAMETA, NUM_MES2FINAMETA,NUM_MES3FINAMETA," +
                    "NUM_MES4FINAMETA, NUM_MES5FINAMETA, NUM_MES6FINAMETA," +
                    "NUM_MES7FINAMETA, NUM_MES8FINAMETA, NUM_MES9FINAMETA," +
                    "NUM_MES10FINAMETA, NUM_MES11FINAMETA, NUM_MES12FINAMETA from ME_FINAMETA";
            rs = stmt.executeQuery(sql);
            FinaMetaVO bean = null;
            while (rs.next()) {
                bean = new FinaMetaVO();
                setFinaMeta(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public int getMuestrafin(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FinaMetaVO> lista = new ArrayList<FinaMetaVO>(0);
        int numero=0;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select NUM_CODFINAMETA_PK from ME_FINAMETA WHERE NUM_CODACTPER_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            FinaMetaVO bean = null;
            while (rs.next()) {
                bean = new FinaMetaVO();
                setMuestraF(bean, rs);
                lista.add(bean);
                numero=1;
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return numero;
    }



    public int getMuestrafinMod(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<FinaMetaVO> lista = new ArrayList<FinaMetaVO>(0);
        int numero=0;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select NUM_CODFINAMETAM_PK from ME_FINAMETAM WHERE NUM_CODACTPER_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            FinaMetaVO bean = null;
            while (rs.next()) {
                bean = new FinaMetaVO();
                setMuestraFM(bean, rs);
                lista.add(bean);
                numero=1;
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return numero;
    }



    public List getFiltro(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        Connection conn = null;
        ArrayList<FinaMetaVO> lista = new ArrayList<FinaMetaVO>(0);
        //lista=null;
        String sql = "";
        try {
        	conn = DriverManager.getConnection(url, usuario, password);
        	
            sql = "select NUM_MES1FINAMETA,NUM_MES2FINAMETA,NUM_MES3FINAMETA,NUM_MES4FINAMETA,NUM_MES5FINAMETA" +
            		",NUM_MES6FINAMETA,NUM_MES7FINAMETA,NUM_MES8FINAMETA,NUM_MES8FINAMETA,NUM_MES9FINAMETA,NUM_MES10FINAMETA" +
            		",NUM_MES11FINAMETA,NUM_MES12FINAMETA ," +
            
            		"sum(f.NUM_MES1FINAMETA+ f.NUM_MES2FINAMETA+f.NUM_MES3FINAMETA+" +
            		" f.NUM_MES4FINAMETA+ f.NUM_MES5FINAMETA+ f.NUM_MES6FINAMETA+" +
            		" f.NUM_MES7FINAMETA+ f.NUM_MES8FINAMETA+ f.NUM_MES9FINAMETA+" +
            		"  f.NUM_MES10FINAMETA+ f.NUM_MES11FINAMETA+ f.NUM_MES12FINAMETA ) as PPto_Anual , sum(  f.NUM_MES1FINAMETA+ f.NUM_MES2FINAMETA+f.NUM_MES3FINAMETA+" +
            		" f.NUM_MES4FINAMETA+ f.NUM_MES5FINAMETA+ f.NUM_MES6FINAMETA ) as SemestreI," +
            		"   sum(f.NUM_MES7FINAMETA+ f.NUM_MES8FINAMETA+ f.NUM_MES9FINAMETA+" +
            		"   f.NUM_MES10FINAMETA+ f.NUM_MES11FINAMETA+ f.NUM_MES12FINAMETA ) as SemestreII" +
            		" ,f.NUM_CODFINAMETA_PK as id ,act.TX_DESCACTPER as descAct,act.NUM_CODACTPER_PK as idAct from ME_FINAMETA f" +
            		"   inner join ME_ACTPER act" +
            		"   on f.NUM_CODACTPER_PK=act.NUM_CODACTPER_PK" +
            		"   where f.NUM_CODACTPER_PK=?" +
            		"  group by f.NUM_CODFINAMETA_PK,act.TX_DESCACTPER,act.NUM_CODACTPER_PK,NUM_MES1FINAMETA,NUM_MES2FINAMETA,NUM_MES3FINAMETA,NUM_MES4FINAMETA,NUM_MES5FINAMETA,NUM_MES6FINAMETA,NUM_MES7FINAMETA,NUM_MES8FINAMETA,NUM_MES8FINAMETA,NUM_MES9FINAMETA,NUM_MES10FINAMETA" +
            		",NUM_MES11FINAMETA,NUM_MES12FINAMETA ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            FinaMetaVO bean = null;
            while (rs.next()) {
                bean = new FinaMetaVO();
                setFinaMeta(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }




    public List getFiltroM(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        Connection conn = null;
        ArrayList<FinaMetaMVO> lista = new ArrayList<FinaMetaMVO>(0);
        //lista=null;
        String sql = "";
        try {
        	conn = DriverManager.getConnection(url, usuario, password);

            sql = "select NUM_MES1FINAMETAM,NUM_MES2FINAMETAM,NUM_MES3FINAMETAM,NUM_MES4FINAMETAM,NUM_MES5FINAMETAM" +
            		",NUM_MES6FINAMETAM,NUM_MES7FINAMETAM,NUM_MES8FINAMETAM,NUM_MES8FINAMETAM,NUM_MES9FINAMETAM,NUM_MES10FINAMETAM" +
            		",NUM_MES11FINAMETAM,NUM_MES12FINAMETAM ," +

            		"sum(f.NUM_MES1FINAMETAM+ f.NUM_MES2FINAMETAM+f.NUM_MES3FINAMETAM+" +
            		" f.NUM_MES4FINAMETAM+ f.NUM_MES5FINAMETAM+ f.NUM_MES6FINAMETAM+" +
            		" f.NUM_MES7FINAMETAM+ f.NUM_MES8FINAMETAM+ f.NUM_MES9FINAMETAM+" +
            		"  f.NUM_MES10FINAMETAM+ f.NUM_MES11FINAMETAM+ f.NUM_MES12FINAMETAM ) as PPto_Anual , sum(  f.NUM_MES1FINAMETAM+ f.NUM_MES2FINAMETAM+f.NUM_MES3FINAMETAM+" +
            		" f.NUM_MES4FINAMETAM+ f.NUM_MES5FINAMETAM+ f.NUM_MES6FINAMETAM ) as SemestreI," +
            		"   sum(f.NUM_MES7FINAMETAM+ f.NUM_MES8FINAMETAM+ f.NUM_MES9FINAMETAM+" +
            		"   f.NUM_MES10FINAMETAM+ f.NUM_MES11FINAMETAM+ f.NUM_MES12FINAMETAM ) as SemestreII" +
            		" ,f.NUM_CODFINAMETAM_PK as id ,act.TX_DESCACTPER as descAct,act.NUM_CODACTPER_PK as idAct from ME_FINAMETAM f" +
            		"   inner join ME_ACTPER act" +
            		"   on f.NUM_CODACTPER_PK=act.NUM_CODACTPER_PK" +
            		"   where f.NUM_CODACTPER_PK=?" +
            		"  group by f.NUM_CODFINAMETAM_PK,act.TX_DESCACTPER,act.NUM_CODACTPER_PK,NUM_MES1FINAMETAM,NUM_MES2FINAMETAM,NUM_MES3FINAMETAM,NUM_MES4FINAMETAM,NUM_MES5FINAMETAM,NUM_MES6FINAMETAM,NUM_MES7FINAMETAM,NUM_MES8FINAMETAM,NUM_MES8FINAMETAM,NUM_MES9FINAMETAM,NUM_MES10FINAMETAM" +
            		",NUM_MES11FINAMETAM,NUM_MES12FINAMETAM ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            FinaMetaMVO bean = null;
            while (rs.next()) {
                bean = new FinaMetaMVO();
                setFinaMetaM(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


   /*   public OEstrategicoVO selectById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Connection conn = null;
        OEstrategicoVO bean = null;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            String sql = "select NUM_CODOBJ_PK,TX_DESCOBJ from ME_OBJ where NUM_CODOBJ_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            bean = new OEstrategicoVO();
            if (rs.next()) {
                setOEstrategico(bean, rs);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }

        return bean;
    }
    *
    *
    */

}
